import os.path
import pandas as pd
from mailmerge import MailMerge
import pymysql as db
import sqlalchemy as sa


def vtoBig(v):
    bigA = ["０","一","二","三","四","五","六","七","八","九"]
    s = str(v)
    r = ''
    for i in range(len(s)):
        r = r + bigA[int(s[i])]
    return r


def genWord(year, chongFan, docx, recordPerPage):
    if chongFan == '消災':
        cond = "1"
    elif chongFan== '太歲':
        cond = "(b.chongFan='太歲' or  b.chongFan='歲破')"
    else:
        cond = f"b.chongFan='{chongFan}'"
    
    sql =f"select b.chongFan,a.name, a.byear, a.bmonth, a.bdate,a.address,a.memo " 
    sql =f"{sql} from data a inner join chongFanTbl b on a.chongFanId = b.id" 
    sql =f"{sql} where a.payed=1 and a.year={year} and {cond} order by a.address"
    df = pd.read_sql(sql, conn)
    list_temp = []
    i=0
    content = {}
    lastAddr=''
    for [t,name,y,m,d,addr,memo] in df.values.tolist():

        if (i==0): 
            lastAddr = ''
            content = {}
        
        ft = f"t{i}"
        fname = f"n{i}"
        fy = f'y{i}'
        fm = f'm{i}'
        fd = f'd{i}'
        faddr = f'a{i}'
        content['year'] = vtoBig(year)
        content['chongFan'] = chongFan
        content[ft] = t
        content[fname] = name

        y = '' if pd.isna(y) else f"{y}"
        m = '' if pd.isna(m) else  f"{m}"
        d = '' if pd.isna(d) else f"{d}"
        content[fy] = f"{y}\n{m}\n{d}"

        if (pd.isna(addr)):
            addr = ''
        if (addr==lastAddr and addr != ''):
            lastAddr = addr
            addr = '同右'
        else:
            lastAddr = addr
    
        content[faddr] = addr
        
        i= (i+1) % recordPerPage
        if (i==0): list_temp.append(content)
    if (content): list_temp.append(content)

    with MailMerge(docx) as doc:
        doc.merge_templates(list_temp, separator='page_break')
        output = f"finalTable\{chongFan}.docx"
        doc.write(output)
    print(f"{chongFan} 祭解疏文產生...成功")

conn = db.connect(host='134.208.6.50',user='root', password='@zaq1xsw2@', database='cihuiTemple')
allTable = ['太歲', '喪門', '五鬼', '死符','白虎','天狗','病符']
year = 113

for chongFan in allTable:
    if chongFan != '消災':
        recordPerPage = 19
        docx = 'template\各類疏文表.docx'
    else:
        recordPerPage = 20
        docx ='template\消災疏文表.docx'

    genWord(year, chongFan, docx, recordPerPage)    

